package com.rt.common.utils.file;

import java.util.*;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;

/**
 * 导出Excel公共方法
 *
 * @author lihao
 * @version 1.0
 */
public class ExportExcel {
    //    数据
    private List<List> dataList;

    //构造方法，传入要导出的数据
    public ExportExcel(List<List> dataList) {
        this.dataList = dataList;
    }

    /*
     * 导出数据
     * */
    public HSSFWorkbook export() throws Exception {
        try {
            HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
            HSSFSheet sheet = workbook.createSheet();
            sheet.setColumnWidth(0, 256 * 35);
            int columnNum = dataList.get(0).size() - 5;
//
            final HSSFCellStyle style = this.getStyle(workbook);
            final HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);
            final HSSFCellStyle cellbStyle = this.getBStyle(workbook);
            // 创建行
            HSSFRow row1 = sheet.createRow(0);
            HSSFRow row2 = sheet.createRow(1);
            row1.setRowStyle(columnTopStyle);
            row2.setRowStyle(columnTopStyle);
            //创建单元格（excel的单元格，参数为列索引，可以是0～255之间的任何一个
            HSSFCell cell = row1.createCell(0);
            cell.setCellValue("地点");
            cell.setCellStyle(columnTopStyle);
//            合并
            sheet.addMergedRegion(new CellRangeAddress(0, 1, 0, 0));// 定义所需列数
//            第一行循环
            for (int i = 1; i <= columnNum / 4; i++) {
                if (i == 1) {
                    HSSFCell cell1 = row1.createCell(1);
                    cell1.setCellValue("第1场");
                    cell1.setCellStyle(columnTopStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, 1, 4));
                } else {
                    HSSFCell cell1 = row1.createCell((((i - 1) * 4) + 1));
                    cell1.setCellValue("第" + i + "场");
                    cell1.setCellStyle(columnTopStyle);
                    sheet.addMergedRegion(new CellRangeAddress(0, 0, ((i - 1) * 4) + 1, 4 * i));
                }
            }
//            第二行表头
            for (int i = 1; i <= columnNum; ) {
                HSSFCell cell1 = row2.createCell(i);
                //设置单元格内容
                cell1.setCellValue("应到");
                cell1.setCellStyle(columnTopStyle);
                i = i + 1;
                HSSFCell cell2 = row2.createCell(i);
                //设置单元格内容
                cell2.setCellValue("实到");
                cell2.setCellStyle(columnTopStyle);
                i = i + 1;
                HSSFCell cell3 = row2.createCell(i);
                //设置单元格内容
                cell3.setCellValue("缺考");
                cell3.setCellStyle(columnTopStyle);
                i = i + 1;
                HSSFCell cell4 = row2.createCell(i);
                //设置单元格内容
                cell4.setCellValue("到考率");
                cell4.setCellStyle(columnTopStyle);
                i = i + 1;
            }
            // 总计
            if (columnNum > 3) {
                HSSFCell zhongji = row1.createCell(columnNum + 1);
                zhongji.setCellValue("合计");
                zhongji.setCellStyle(columnTopStyle);
                sheet.addMergedRegion(new CellRangeAddress(0, 0, columnNum + 1, columnNum + 4));
                HSSFCell cell01 = row2.createCell(columnNum + 1);
                //设置单元格内容
                cell01.setCellValue("应到");
                cell01.setCellStyle(columnTopStyle);
                HSSFCell cell02 = row2.createCell(columnNum + 2);
                //设置单元格内容
                cell02.setCellValue("实到");
                cell02.setCellStyle(columnTopStyle);
                HSSFCell cell03 = row2.createCell(columnNum + 3);
                //设置单元格内容
                cell03.setCellValue("缺考");
                cell03.setCellStyle(columnTopStyle);
                HSSFCell cell04 = row2.createCell(columnNum + 4);
                //设置单元格内容
                cell04.setCellValue("到考率");
                cell04.setCellStyle(columnTopStyle);
                HSSFRow rowRowName = sheet.createRow(2);                // 在索引3的位置创建行(最顶端的行开始的第3行)
                rowRowName.setHeight((short) (25 * 25)); //设置高度
            }
            //将查询出的数据设置到sheet对应的单元格中
            for (int i = 0; i < dataList.size(); i++) {

                List list = dataList.get(i);//遍历每个对象
                HSSFRow row = sheet.createRow(i + 2);//创建所需的行数

                row.setHeight((short) (25 * 20)); //设置高度
                row.setRowStyle(columnTopStyle);
                //obj 结果集
                for (int j = 0; j < list.size(); j++) {

                    HSSFCell rcell = null;  //设置单元格的数据类型
                    if (j == 0) {
                        rcell = row.createCell(j, CellType.STRING);
                        rcell.setCellValue(list.get(0).toString());
                    } else if (j % 4 == 0) {
                        rcell = row.createCell(j, CellType.STRING);
                        rcell.setCellValue(list.get(j).toString());
                    } else if ((j + 1) % 4 == 0) {
                        rcell = row.createCell(j, CellType.STRING);
                        rcell.setCellValue(list.get(j).toString());
                    } else {
                        if (!"".equals(list.get(j)) && list.get(j) != null) {
                            rcell = row.createCell(j, CellType.NUMERIC);
                            rcell.setCellValue(Double.valueOf(list.get(j) + ""));                        //设置单元格的值
                        }
                    }
                    rcell.setCellStyle(style);                                    //设置单元格样式
                }
            }
            String[] letter = {"A", "B", "C", "D", "E", "F", "G",
                    "H", "I", "J", "K", "L", "M", "N",
                    "O", "P", "Q", "R", "S", "T",
                    "U", "V", "W", "X", "Y", "Z",
                    "AA", "AB", "AC", "AD", "AE", "AF", "AG",
                    "AH", "AI", "AJ", "AK", "AL", "AM", "AN",
                    "AO", "AP", "AQ", "AR", "AS", "AT",
                    "AU", "AV", "AW", "AX", "AY", "AZ"};
            //最后一行总计
            if (columnNum > 3) {
                HSSFRow row = sheet.createRow(dataList.size() + 2);
                String formula;
                HSSFCell zjcell = row.createCell(0);
                zjcell.setCellValue("总计");
                zjcell.setCellStyle(style);
                row.setRowStyle(style);
                for (int i = 1; i < columnNum + 5; i++) {
                    HSSFCell cell01 = row.createCell(i);
                    if (i% 4 ==0 &&(i-1)>0) {

                        formula = letter[i-2] + ""+(dataList.size() + 3) +"/"+ letter[i-3] + "" + (dataList.size() + 3) ;
                        //设置单元格内容
                        cell01.setCellFormula(formula);
                        cell01.setCellStyle(cellbStyle);
                    } else if (i==0) {

                    } else {
                        formula = "SUM(" + letter[i] + "3:" + letter[i] + "" + (dataList.size() + 2) + ")";
                        //设置单元格内容
                        cell01.setCellFormula(formula);
                        cell01.setCellStyle(style);
                    }
                }

            }
            //让列宽随着导出的列长自动适应
            for (int colNum = 0; colNum < columnNum; colNum++) {
                int columnWidth = sheet.getColumnWidth(colNum) / 256;
                for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
                    HSSFRow currentRow;
                    //当前行未被使用过
                    if (sheet.getRow(rowNum) == null) {
                        currentRow = sheet.createRow(rowNum);
                    } else {
                        currentRow = sheet.getRow(rowNum);
                    }
                    if (currentRow.getCell(colNum) != null) {
                        HSSFCell currentCell = currentRow.getCell(colNum);
                        if (currentCell.getCellType() == CellType.STRING) {
                            int length = currentCell.getStringCellValue().getBytes().length;
                            if (columnWidth < length) {
                                columnWidth = length;
                            }
                        }
                    }
                }
                if (colNum == 0) {
                    sheet.setColumnWidth(colNum, (columnWidth - 2) * 128);
                } else {
                    sheet.setColumnWidth(colNum, (columnWidth + 4) * 256);
                }


            }
            if (workbook != null) {
                try {
                    // FileOutputStream out = new FileOutputStream("D:/" + new SimpleDateFormat("yyyyMMddHHmmss").format(new Date()).toString() + ".xlsx");
//                    workbook.write();
                    // out.close();
                    return workbook;
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return null;
    }

    /*
     * 表头样式
     */
    public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

        // 设置字体
        Font font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 500);
        //字体加粗
        font.setFontHeight((short) 200);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setFillForegroundColor(IndexedColors.RED.getIndex()); // 前景色
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        return style;

    }

    /*
     * 单元格样式
     */
    public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        return style;
    }

    /*
     * 单元格样式
     */
    public HSSFCellStyle getBStyle(HSSFWorkbook workbook) {
        // 设置字体
        HSSFFont font = workbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //设置字体名字
        font.setFontName("Courier New");
        //设置样式;
        HSSFCellStyle style = workbook.createCellStyle();
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.GREEN.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK.getIndex());

        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK.getIndex());
        style.setAlignment(HorizontalAlignment.CENTER);//水平居中
        style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0.00%"));
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(false);
        return style;
    }
}